-- Data for Obligatory Exercise 1, 5DV120, Fall 2012.
-- Last updated, 09.09.2012.

-- For Problem 1(a), run the following testing script:

CREATE OR REPLACE FUNCTION test1(student_id VARCHAR(5))
    RETURNS INTEGER AS $$
      SELECT check_advisor(s_id)
      FROM   advisor
      WHERE (s_id = $1);
    $$ LANGUAGE SQL;

INSERT INTO advisor VALUES ('19991',NULL);
SELECT test1('19991');

UPDATE advisor SET i_id='32343' WHERE s_id='19991';
SELECT test1('19991');

UPDATE advisor SET i_id='33456' WHERE s_id='19991';
SELECT test1('19991');

UPDATE instructor SET dept_name=NULL WHERE ID='33456';
SELECT test1('19991');

UPDATE instructor SET dept_name='History' WHERE ID='33456';
SELECT test1('19991');

UPDATE student SET dept_name=NULL WHERE ID='19991';
SELECT test1('19991');

UPDATE student SET dept_name='Physics' WHERE ID='19991';
SELECT test1('19991');

UPDATE instructor SET dept_name='Finance' WHERE ID='33456';
SELECT test1('19991');

UPDATE instructor SET dept_name='Physics' WHERE ID='33456';
SELECT test1('19991');

DELETE FROM advisor WHERE s_id='19991';
SELECT test1('19991');

INSERT INTO advisor VALUES('19991','33456');
SELECT test1('19991');

DELETE FROM advisor WHERE s_id='19991';
SELECT test1('19991');

-- end of testing script for Problem 1(a).

